package com.nbsaas.boot.sql;

import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.expression.operators.relational.GreaterThan;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SelectItem;
import org.hibernate.engine.jdbc.internal.FormatStyle;

import java.util.ArrayList;
import java.util.List;

public class CteConditionExample {
    public static void main(String[] args) throws Exception {
        String sql = """
                WITH sales_cte AS (
                  SELECT category, SUM(amount) AS total_sales
                  FROM orders
                  GROUP BY category
                ),
                sales_cte2 AS (
                  SELECT category, SUM(amount) AS total_sales
                  FROM orders
                  GROUP BY category
                )
                SELECT category, total_sales
                FROM sales_cte order by category desc
                """;

        // 解析 SQL
        Statement stmt = CCJSqlParserUtil.parse(sql);
        Select select = (Select) stmt;
        PlainSelect body = (PlainSelect) select.getSelectBody();

        // 构造一个条件 total_sales > 1000
        GreaterThan condition = new GreaterThan();
        condition.setLeftExpression(new Column("total_sales"));
        condition.setRightExpression(new LongValue(1000));

        // 如果原来没有 where，直接设置；否则用 And 拼接
        if (body.getWhere() == null) {
            body.setWhere(condition);
        } else {
            body.setWhere(new AndExpression(body.getWhere(), condition));
        }

        // 输出修改后的 SQL
        System.out.println(select.toString());


        Expression columnExpr = new Column("*");

        ExpressionList<Expression> functionArgs = new ExpressionList<>(columnExpr);
        net.sf.jsqlparser.expression.Function function = new net.sf.jsqlparser.expression.Function(
                "count",
                functionArgs
        );
        List<SelectItem<?>> selectItems = new ArrayList<>();

        Alias alias = new Alias("xxxx");
        SelectItem<?> selectItem = new SelectItem<>(function, alias);
        selectItems.add(selectItem);
        body.setSelectItems(selectItems);
        body.setOrderByElements(null);

        System.out.println(body.toString());

        //String str = FormatStyle.BASIC.getFormatter().format(select.toString());
        //System.out.println(str);
    }
}
